package school.repository;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.Date;
import java.sql.ResultSet;
import java.sql.Types;
import java.text.SimpleDateFormat;
import java.util.ArrayList;

import org.springframework.security.userdetails.UserDetails;

import school.domain.Staff;

public class JdbcStaffDao implements StaffDao {

	@Override
	public UserDetails getByUsername(String username) throws Exception {
		Connection con = JdbcUtil.connect();
		Staff staff = new Staff();
		int rows;
		Date dateLeftStaff;

		SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");

		String sql = "CALL Retrieve_Staff(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
		CallableStatement prepCall = con.prepareCall(sql);

		prepCall.setString(1, username);
		prepCall.registerOutParameter(2, Types.VARCHAR);
		prepCall.registerOutParameter(3, Types.VARCHAR);
		prepCall.registerOutParameter(4, Types.VARCHAR);
		prepCall.registerOutParameter(5, Types.VARCHAR);
		prepCall.registerOutParameter(6, Types.VARCHAR);
		prepCall.registerOutParameter(7, Types.VARCHAR);
		prepCall.registerOutParameter(8, Types.VARCHAR);
		prepCall.registerOutParameter(9, Types.VARCHAR);
		prepCall.registerOutParameter(10, Types.DATE);
		prepCall.registerOutParameter(11, Types.DATE);
		prepCall.registerOutParameter(12, Types.DATE);
		prepCall.registerOutParameter(13, Types.VARCHAR);
		prepCall.registerOutParameter(14, Types.VARCHAR);
		prepCall.registerOutParameter(15, Types.INTEGER);
		prepCall.registerOutParameter(16, Types.INTEGER);

		try {
			prepCall.execute();
			rows = prepCall.getInt(16);

			if (rows == 1) {
				staff.setUsername(username);
				staff.setPassword(prepCall.getString(2));
				staff.setFirstName(prepCall.getString(3));
				staff.setLastName(prepCall.getString(4));
				staff.setStreet(prepCall.getString(5));
				staff.setBuildingNumber(prepCall.getString(6));
				staff.setApartmentNumber(prepCall.getString(7));
				staff.setCity(prepCall.getString(8));
				staff.setPostalCode(prepCall.getString(9));

				staff.setDateOfBirth(dateFormat.format(prepCall.getDate(10)));
				staff.setDateJoinedStaff(dateFormat.format(prepCall.getDate(11)));

				dateLeftStaff = prepCall.getDate(12);
				if (dateLeftStaff != null) {
					staff.setDateLeftStaff(dateFormat.format(dateLeftStaff));
				}

				staff.setEmail(prepCall.getString(13));
				staff.setPhoneNumber(prepCall.getString(14));
				staff.setId(prepCall.getInt(15));

				return staff;
			} else
				return null;
		} catch (Exception e) {
			throw e;
		} finally {
			JdbcUtil.disconnect();
		}
	}

	@Override
	public Staff getById(int staffId) throws Exception {
		Connection con = JdbcUtil.connect();
		Staff staff = new Staff();
		int rows;
		Date dateLeftStaff;

		SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");

		String sql = "CALL Retrieve_Staff_By_Id(?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
		CallableStatement prepCall = con.prepareCall(sql);

		prepCall.setInt(1, staffId);
		prepCall.registerOutParameter(2, Types.VARCHAR);
		prepCall.registerOutParameter(3, Types.VARCHAR);
		prepCall.registerOutParameter(4, Types.VARCHAR);
		prepCall.registerOutParameter(5, Types.VARCHAR);
		prepCall.registerOutParameter(6, Types.VARCHAR);
		prepCall.registerOutParameter(7, Types.VARCHAR);
		prepCall.registerOutParameter(8, Types.VARCHAR);
		prepCall.registerOutParameter(9, Types.DATE);
		prepCall.registerOutParameter(10, Types.DATE);
		prepCall.registerOutParameter(11, Types.DATE);
		prepCall.registerOutParameter(12, Types.VARCHAR);
		prepCall.registerOutParameter(13, Types.VARCHAR);
		prepCall.registerOutParameter(14, Types.INTEGER);

		try {
			prepCall.execute();
			rows = prepCall.getInt(14);

			if (rows == 1) {
				staff.setId(staffId);
				staff.setFirstName(prepCall.getString(2));
				staff.setLastName(prepCall.getString(3));
				staff.setStreet(prepCall.getString(4));
				staff.setBuildingNumber(prepCall.getString(5));
				staff.setApartmentNumber(prepCall.getString(6));
				staff.setCity(prepCall.getString(7));
				staff.setPostalCode(prepCall.getString(8));

				staff.setDateOfBirth(dateFormat.format(prepCall.getDate(9)));
				staff.setDateJoinedStaff(dateFormat.format(prepCall.getDate(10)));

				dateLeftStaff = prepCall.getDate(11);
				if (dateLeftStaff != null) {
					staff.setDateLeftStaff(dateFormat.format(dateLeftStaff));
				}

				staff.setEmail(prepCall.getString(12));
				staff.setPhoneNumber(prepCall.getString(13));

				return staff;
			} else
				return null;
		} catch (Exception e) {
			throw e;
		} finally {
			JdbcUtil.disconnect();
		}
	}

	@Override
	public ArrayList<Staff> getWholeStaff() throws Exception {
		Connection con = JdbcUtil.connect();
		ArrayList<Staff> staff = new ArrayList<Staff>();

		String sql = "CALL Get_Whole_Staff";
		CallableStatement prepCall = con.prepareCall(sql);

		try {
			ResultSet rs = prepCall.executeQuery();
			
			while(rs.next()) {
				Staff staffMember = new Staff();
				staffMember.setId(rs.getInt(1));
				staffMember.setFirstName(rs.getString(2));
				staffMember.setLastName(rs.getString(3));
				
				staff.add(staffMember);
			}
		} catch (Exception e) {
			throw e;
		} finally {
			JdbcUtil.disconnect();
		}

		return staff;
	}

}
